using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.Collections;
using DTO;

namespace DAO
{
    public class MuaSachDao
    {
        #region Methods
        public DataSet LayBang()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From MUA_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);   // can thiet
            da.Fill(ds);
            //B5: Dong ket noi CSDL
            cn.Close();
            return ds;
        }



        public IList LayDanhSach()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From MUA_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            MuaSachDto ms = null;
            ArrayList ds = new ArrayList();
            while (dr.Read())
            {
                ms = new MuaSachDto();
                ms.MaMuaSach = (int)dr["MaMuaSach"];
                ms.MaDauSach = (int)dr["MaDauSach"];
                ms.MaKH = (int)dr["MaKhachHang"];
                ms.NgayMua = (DateTime)dr["NgayMua"];
                ms.GhiChu = (string)dr["GhiChu"];

                ds.Add(ms);
            }

            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }



        public void CapNhatBang(DataSet ds)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From MUA_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);  // khi cap nhat bang phai co dong lenh nay
            da.Update(ds.Tables[0]);
            //B5: Dong ket noi CSDL
            cn.Close();
        }



        public MuaSachDto TimKiem(int maMuaSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From MUA_SACH Where MaMuaSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaMuaSach", OleDbType.Integer);
            cmd.Parameters["@MaMuaSach"].Value = maMuaSach;
            OleDbDataReader dr = cmd.ExecuteReader();
            MuaSachDto ms = null;
            while (dr.Read())
            {
                ms = new MuaSachDto();
                ms.MaMuaSach = (int)dr["MaMuaSach"];
                ms.MaDauSach = (int)dr["MaDauSach"];
                ms.MaKH = (int)dr["MaKhachHang"];
                ms.NgayMua = (DateTime)dr["NgayMua"];
                ms.GhiChu = (string)dr["GhiChu"];
            }

            //B5: Dong ket noi CSDL
            cn.Close();
            return ms;
        }



        public void Them(MuaSachDto ms)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into MUA_SACH(MaDauSach, MaKhachHang, NgayMua, GhiChu) values(?, ?, ?, ?)";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters.Add("@MaKhachHang", OleDbType.Integer);
            cmd.Parameters.Add("@NgayMua", OleDbType.Date);
            cmd.Parameters.Add("@GhiChu", OleDbType.WChar);

            cmd.Parameters["@MaDauSach"].Value = ms.MaDauSach;
            cmd.Parameters["@MaKhachHang"].Value = ms.MaKH;
            cmd.Parameters["@NgayMua"].Value = ms.NgayMua;
            cmd.Parameters["@GhiChu"].Value = ms.GhiChu;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            ms.MaMuaSach = (int)cmd.ExecuteScalar();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Xoa(int maDauSach, int MaKhachHang)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From MUA_SACH Where MaDauSach = ? and MaKhachHang = ? ";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters.Add("@MaKhachHang", OleDbType.Integer);
            cmd.Parameters["@MaDauSach"].Value = maDauSach;
            cmd.Parameters["@MaKhachHang"].Value = MaKhachHang;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Sua(MuaSachDto ms)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Update MUA_SACH set MaDauSach = ?, MaKhachHang = ?, NgayMua = ?, GhiChu = ? where = MaMuaSach = ?";

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters.Add("@MaKhachHang", OleDbType.Integer);
            cmd.Parameters.Add("@NgayMua", OleDbType.Date);
            cmd.Parameters.Add("@GhiChu", OleDbType.WChar);

            cmd.Parameters["@MaDauSach"].Value = ms.MaDauSach;
            cmd.Parameters["@MaKhachHang"].Value = ms.MaKH;
            cmd.Parameters["@NgayMua"].Value = ms.NgayMua;
            cmd.Parameters["@GhiChu"].Value = ms.GhiChu;
            cmd.ExecuteNonQuery();

            //B5: Dong ket noi CSDL
            cn.Close();
        }
        public DataTable ThongKe(string strSQL)
        {
            DataTable dtDS = new DataTable();
            OleDbConnection oledbcnKetnoi = DataProvider.ConnectionData();
            
            OleDbCommand oledbcmTruyvan = new OleDbCommand(strSQL, oledbcnKetnoi);
            OleDbDataAdapter oledbadpt = new OleDbDataAdapter(oledbcmTruyvan);

            //oledbadpt.FillSchema(dtDS, SchemaType.Source);
            oledbadpt.Fill(dtDS);


            oledbcnKetnoi.Close();
            oledbcnKetnoi.Dispose();
            oledbcmTruyvan.Dispose();
            oledbadpt.Dispose();

            return dtDS;
        }
        #endregion Methods

    }
}
